#include "dboperate.h"
#include <QSqlQuery>
#include <QVector>
#include <QVariant>
#include <QDebug>

DBOperate::DBOperate()
{
    if (QSqlDatabase::contains("qt_sql_default_connection"))
    {
        db = QSqlDatabase::database("qt_sql_default_connection");
    }
    else
    {
        db = QSqlDatabase::addDatabase("QSQLITE");
        db.setDatabaseName("sqlite.db");
        db.open();
    }
    createTable();
}

bool DBOperate::createAccount(QString userName)
{
    QSqlQuery query(db);
    QString sql1 = QString("select uuid from accountsInfo where userName = '%1'").arg(userName);
    QString sql2 = QString("select max(uuid) uuid from accountsInfo");
    QString sql3 = "insert into accountsInfo (uuid, userName, password, rule, firstLogin) values (%1, '%2', '88888888', 0, 0)";
    int maxID = 0;
    if (!query.exec(sql1))
    {
        qDebug()<<"添加用户失败";
        return false;
    }

    if (query.next())//判断用户名是否被使用
    {
        qDebug()<<"用户名已被使用";
        return false;
    }

    if (!query.exec(sql2))
    {
        qDebug()<<"添加用户失败";
        return false;
    }

    if (query.next())//判断是否有最大uuid
    {
        maxID = query.value("uuid").toInt();
    }
    else
    {
        return false;
    }

    if (query.exec(sql3.arg(maxID + 1).arg(userName)))
    {
        //qDebug()<<"创建用户成功";
        return true;
    }
    return false;
}

bool DBOperate::changePassword(int uuid, QString password)
{
    QSqlQuery query(db);
    QString sql = QString("update accountsInfo set password = '%1' where uuid = '%2'").arg(password).arg(uuid);
    if (!query.exec(sql))
    {
        //qDebug()<<"更新密码失败";
        return false;
    }
    else {
        //qDebug()<<"更新密码成功";
        return true;
    }
    return false;
}

bool DBOperate::deleteAccount(int uuid)
{
    QSqlQuery query(db);
    QString sql = QString("delete from accountsInfo where uuid = '%1'").arg(uuid);
    if (!query.exec(sql))
    {
        //qDebug()<<"删除账号失败";
        return false;
    }
    else {
        //qDebug()<<"删除账号成功";
        return true;
    }
    return false;
}

bool DBOperate::changeFirstLoginState(int uuid, int state)
{
    QSqlQuery query(db);
    QString sql = QString("update accountsInfo set firstLogin = %1 where uuid = %2").arg(state).arg(uuid);
    if (!query.exec(sql))
    {
        //qDebug()<<"更改第一次登陆状态失败";
        return false;
    }
    else {
        //qDebug()<<"更改第一次登陆状态成功";
        return true;
    }
    return false;
}

AccountInfo DBOperate::selectAccountInfo(QString userName, QString password)
{
    AccountInfo accountInfo;
    accountInfo.initData();
    QSqlQuery query(db);
    QString sql = QString("select * from accountsInfo where userName = '%1' and password = '%2'").arg(userName).arg(password);
    if (!query.exec(sql))
    {
        //qDebug()<<"查询账号信息失败";
    }
    else {
        //qDebug()<<"查询账号信息成功";
    }
    if (query.next())
    {
        accountInfo.uuid = query.value("uuid").toInt();
        accountInfo.userName = query.value("userName").toString();
        switch (query.value("rule").toInt())
        {
            case 0:
                accountInfo.rule = Rule::Ordinary;
                break;
            case 1:
                accountInfo.rule = Rule::Administrator;
                break;
            default:
                accountInfo.rule = Rule::NotLogin;
                break;
        }
        accountInfo.password = query.value("password").toString();
        accountInfo.firstLogin = query.value("firstLogin").toInt();
    }
    return accountInfo;
}

QVector<AccountInfo> DBOperate::selectAccountsInfo()
{
    QVector<AccountInfo> accountsInfo;
    QSqlQuery query(db);
    QString sql = "select * from accountsInfo";
    if (!query.exec(sql))
    {
        //qDebug()<<"查询账号信息失败";
    }
    else {
        //qDebug()<<"查询账号信息成功";
    }
    while (query.next())
    {
        AccountInfo accountInfo;
        accountInfo.initData();
        accountInfo.uuid = query.value("uuid").toInt();
        accountInfo.userName = query.value("userName").toString();
        switch (query.value("rule").toInt())
        {
            case 0:
                accountInfo.rule = Rule::Ordinary;
                break;
            case 1:
                accountInfo.rule = Rule::Administrator;
                break;
            default:
                accountInfo.rule = Rule::NotLogin;
                break;
        }
        accountsInfo.append(accountInfo);
    }
    return accountsInfo;
}

QVector<NodeInfo> DBOperate::selectNodes()
{
    QVector<NodeInfo> nodesInfo;
    QSqlQuery query(db);
    QString sql = "select * from nodesInfo";
    if (!query.exec(sql))
    {
        //qDebug()<<"查询监控站点信息失败";
    }
    else {
        //qDebug()<<"查询监控站点信息成功";
    }
    while (query.next())
    {
        NodeInfo nodeInfo;
        nodeInfo.nodeName = query.value("nodeName").toString();
        nodeInfo.ID = query.value("ID").toString();
        nodeInfo.point = query.value("point").toString();
        nodeInfo.affiliatedArea = query.value("affiliatedArea").toString();
        nodesInfo.append(nodeInfo);
    }
    return nodesInfo;
}

QVector<AreaInfo> DBOperate::selectAreas()
{
    QVector<AreaInfo> areasInfo;
    QSqlQuery query(db);
    QString sql = "select * from AreasInfo";
    if (query.exec(sql))
    {
        //qDebug()<<"查询监控区域信息成功";
    }
    else {
        //qDebug()<<"查询监控区域信息失败";
    }
    while (query.next())
    {
        AreaInfo areaInfo;
        areaInfo.areaName = query.value("areaName").toString();
        areaInfo.point = query.value("point").toString();
        areaInfo.imagePath = query.value("imagePath").toString();
        areasInfo.append(areaInfo);
    }
    return areasInfo;
}

void DBOperate::createTable()
{
    QSqlQuery query(db);
    QString sql_1 = QString("select count(*) from sqlite_master where type = 'table' and name='%1'").arg("accountsInfo");
    QString sql_2 = QString("select count(*) from sqlite_master where type = 'table' and name='%1'").arg("areasInfo");
    QString sql_3 = QString("select count(*) from sqlite_master where type = 'table' and name='%1'").arg("nodesInfo");
    QString sql_4 = QString("select count(*) from sqlite_master where type = 'table' and name='%1'").arg("logsInfo");
    if (db.tables().contains("accountsInfo"))
    {
        //qDebug()<<"accountsInfo表存在";
    }
    else
    {
        //qDebug()<<"accountsInfo表不存在,开始创建accountsInfo表";
        QString createTableSql = "create table accountsInfo(uuid int primiry key, userName varchar(12), password varchar(12), rule int, firstLogin int)";
        if (query.exec(createTableSql))
        {
            //qDebug()<<"创建accountsInfo表成功";
        }
        else
        {
            //qDebug()<<"创建accountsInfo表失败";
        }
    }
    if (db.tables().contains("areasInfo"))
    {
        //qDebug()<<"areasInfo表存在";
    }
    else
    {
        //qDebug()<<"areasInfo表不存在,开始创建areasInfo表";
        QString createTableSql = "create table areasInfo(areaName varchar(12), point varchar(12), imagePath varchar(24))";
        if (query.exec(createTableSql))
        {
            //qDebug()<<"创建areasInfo表成功";
        }
        else
        {
            //qDebug()<<"创建areasInfo表失败";
        }
    }
    if (db.tables().contains("nodesInfo"))
    {
        //qDebug()<<"nodesInfo表存在";
    }
    else
    {
        //qDebug()<<"nodesInfo表不存在,开始创建nodesInfo表";
        QString createTableSql = "create table nodesInfo(nodeName varchar(12), ID varchar(12), affiliatedArea varchar(12), point varchar(12))";
        if (query.exec(createTableSql))
        {
            //qDebug()<<"创建nodesInfo表成功";
        }
        else
        {
            //qDebug()<<"创建nodesInfo表失败";
        }
    }
    if (db.tables().contains("logsInfo"))
    {
        //qDebug()<<"logsInfo表存在";
    }
    else
    {
        //qDebug()<<"logsInfo表不存在,开始创建logsInfo表";
        QString createTableSql = "create table logsInfo(nodeName varchar(12), ID varchar(12), affiliatedArea varchar(12), eventType int, date varchar(24), operator varchar(12))";
        if (query.exec(createTableSql))
        {
            //qDebug()<<"创建logsInfo表成功";
        }
        else
        {
            //qDebug()<<"创建logsInfo表失败";
        }
    }
}

void DBOperate::updateAreas(QVector<AreaInfo> areas)
{
    QSqlQuery query(db);
    if (query.exec("select areaName from areasInfo"))
    {
        while (query.next())
        {
            QString areaName = query.value("areaName").toString();
            for (int i = 0; i < areas.length(); i++)
            {
                if (areaName == areas.at(i).areaName)
                {
                    break;
                }
                if (i == areas.length() - 1)
                {
                    query.exec(QString("delete from areasInfo where areaName = '%1'").arg(areaName));
                }
            }
        }
    }
    for (int i = 0; i < areas.length(); i++)
    {
        AreaInfo area = areas.at(i);
        QString sql1 = QString("select areaName from areasInfo where areaName = '%1'");
        QString sql2 = QString("update areasInfo set point = '%1', imagePath = '%2' where areaName = '%3'");
        QString sql3 = QString("insert into nodesInfo (areaName, point, imagePath) values('%1','%2','%3')");
        if (query.exec(sql1.arg(area.areaName)))
        {
            if (query.next())
            {
                query.exec(sql2.arg(area.point).arg(area.imagePath).arg(area.areaName));
            }
            else
            {
                query.exec(sql3.arg(area.areaName).arg(area.point).arg(area.imagePath));
            }
            //qDebug()<<area.areaName<<area.point<<area.imagePath;
        }
    }
}

void DBOperate::updateNodes(QVector<NodeInfo> nodes)
{
    QSqlQuery query(db);
    if (query.exec("select ID from nodesInfo"))
    {
        while (query.next())
        {
            QString ID = query.value("ID").toString();
            for (int i = 0; i < nodes.length(); i++)
            {
                if (ID == nodes.at(i).ID)
                {
                    break;
                }
                if (i == nodes.length() - 1)
                {
                    query.exec(QString("delete from nodesInfo where ID = '%1'").arg(ID));
                }
            }
        }
    }
    for (int i = 0; i < nodes.length(); i++)
    {
        NodeInfo node = nodes.at(i);
        QString sql1 = QString("select nodeName from nodesInfo where ID = '%1'");
        QString sql2 = QString("update nodesInfo set nodeName = '%1', affiliatedArea = '%2', point = '%3' where ID = '%4'");
        QString sql3 = QString("insert into nodesInfo (nodeName, ID, affiliatedArea, point) values('%1','%2','%3','%4')");
        if (query.exec(sql1.arg(node.ID)))
        {
            if (query.next())
            {
                query.exec(sql2.arg(node.nodeName).arg(node.affiliatedArea).arg(node.point).arg(node.ID));
            }
            else
            {
                query.exec(sql3.arg(node.nodeName).arg(node.ID).arg(node.affiliatedArea).arg(node.point));
            }
            //qDebug()<<node.nodeName<<node.ID<<node.affiliatedArea<<node.point;
        }
    }
}

bool DBOperate::insertLog(LogInfo logInfo)
{
    QSqlQuery query(db);
    QString sql = QString("insert into logsInfo (nodeName, affiliatedArea, ID, eventType, eventDescription, date, operator) values('%1', '%2', '%3', %4, '%5', '%6', '%7')");
    if (query.exec(sql.arg(logInfo.nodeName).arg(logInfo.affiliatedArea).arg(logInfo.ID).arg(logInfo.eventType).arg(logInfo.eventDescription).arg(logInfo.date).arg(logInfo.op)))
    {
        return true;
    }
    return false;
}

QVector<LogInfo> DBOperate::selectLogsInfo()
{
    QVector<LogInfo> logsInfo;
    QSqlQuery query(db);
    QString sql = "select * from logsInfo";
    if (query.exec(sql))
    {
        while (query.next())
        {
            LogInfo logInfo;
            logInfo.nodeName = query.value("nodeName").toString();
            logInfo.affiliatedArea = query.value("affiliatedArea").toString();
            logInfo.ID = query.value("ID").toString();
            logInfo.eventType = query.value("eventType").toInt();
            logInfo.eventDescription = query.value("eventDescription").toString();
            logInfo.date = query.value("date").toString();
            logInfo.op = query.value("operator").toString();
            logsInfo.append(logInfo);
        }
    }
    return logsInfo;
}
